package com.anche.demo.excel;

/**
 * @ClassName: ReadTemAndWrite
 * @Description: TODO
 * @date: 2023/2/9 11:54
 * @Auther: hey
 */

//import cn.hutool.core.io.FileUtil;
import com.alibaba.excel.EasyExcel;
import com.anche.demo.entity.User;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Arrays;

public class ReadTemAndWrite {
    /*
    附件类型
 */
    private static final String ANNEX_TYPE = "annex_type";
    public static void main(String[] args) throws IOException {
        ReadTemAndWrite readTemAndWrite=new ReadTemAndWrite();
        readTemAndWrite.test();
        System.out.println(ANNEX_TYPE);
    }

    private  void test() {
        ArrayList<User> users = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
//            users.add(new User("test"+i,i,"男"));
        }
        String destPath = "C:\\Users\\Administrator\\Desktop\\ceshi2.xlsm";
//        FileUtil.copy(path,destPath,true);
//        File fi = new File("C:\\Users\\Administrator\\Desktop\\ceshi.xlsm");
        ClassPathResource classPathResource = new ClassPathResource("ceshi.xlsm");
//        String path = classPathResource.getPath();
        String path="G:\\biaopin\\demo-shiwu\\src\\main\\resources\\ceshi.xlsm";
        File fi = new File(path);

        FileOutputStream fos =null;
        InputStream in = null;
        try {
//            in = new FileInputStream(fi);
//            XSSFWorkbook wb = new XSSFWorkbook(in);
//            XSSFSheet sheet = wb.getSheetAt(0);
//            String[] ss=new String[2];
//            ss[0]="张三";
//            ss[1]="李四";
//            createDropDownList(sheet, ss,0,65535,1,1);
//            fos = new FileOutputStream(destPath);
//            for (int i = 0; i < users.size();i++) {
//                User user = users.get(i);
//                XSSFRow row = sheet.createRow(i + 1);
//                row.createCell(0).setCellValue(user.getName());
//                row.createCell(1).setCellValue(user.getAge());
//                row.createCell(2).setCellValue(user.getSex());
//            }
//            InputStream inputStream = this.getClass().getResourceAsStream("/excel/ceshi.xlsm");//与方法二类似，只不过返回了InputStream类型了
            EasyExcel.write(destPath, User.class).withTemplate(in).sheet(1,"其他附件").doWrite(users);
//            wb.write(fos);
        } finally {
            if (fos!=null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
            if (in!=null) {
                try {
                    in.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 创建下拉列表选项(单元格下拉框数据小于255字节时使用)
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    public static void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof HSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(false);
        } else {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
            dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);

        }
        sheet.addValidationData(dataValidation);
    }

}



